<?php
class ModelLocalisationGeoZone extends Model {
	public function addGeoZone($data) {
		$this->db->query("INSERT INTO  @@geo_zone SET name = '" . ES($data['name']) . "', description = '" . ES($data['description']) . "', date_added = '{$GLOBALS['V5_NOW']}'");

		$geo_zone_id = $this->db->getLastId();

		if (isset($data['zone_to_geo_zone'])) {
			foreach ($data['zone_to_geo_zone'] as $value) {
				$this->db->query("INSERT INTO  @@zone_to_geo_zone SET country_id = "  . (int)$value['country_id'] . ", zone_id = "  . (int)$value['zone_id'] . ", geo_zone_id = "  . $geo_zone_id . ", date_added = '{$GLOBALS['V5_NOW']}'");
			}
		}
	}

	public function editGeoZone($geo_zone_id, $data) {
		$this->db->query("UPDATE  @@geo_zone SET name = '" . ES($data['name']) . "', description = '" . ES($data['description']) . "', date_modified = '{$GLOBALS['V5_NOW']}' WHERE geo_zone_id = " . (int)$geo_zone_id);

		$this->db->query("DELETE FROM  @@zone_to_geo_zone WHERE geo_zone_id = " . (int)$geo_zone_id);

		if (isset($data['zone_to_geo_zone'])) {
			foreach ($data['zone_to_geo_zone'] as $value) {
				$this->db->query("INSERT INTO  @@zone_to_geo_zone SET country_id = "  . (int)$value['country_id'] . ", zone_id = "  . (int)$value['zone_id'] . ", geo_zone_id = "  .(int)$geo_zone_id . ", date_added = '{$GLOBALS['V5_NOW']}'");
			}
		}
	}

	public function deleteGeoZone($geo_zone_id) {
		$this->db->query("DELETE FROM  @@geo_zone WHERE geo_zone_id = " . (int)$geo_zone_id);
		$this->db->query("DELETE FROM  @@zone_to_geo_zone WHERE geo_zone_id = " . (int)$geo_zone_id);
	}

	public function getGeoZone($geo_zone_id) {
		return $this->db->queryOne("SELECT DISTINCT * FROM  @@geo_zone WHERE geo_zone_id = " . (int)$geo_zone_id);
	}

	public function getGeoZones($filter = NULL) {
		$sql = "SELECT * FROM  @@geo_zone";

		if ($filter) {
			$sql .= $filter->sort('name');
			$sql .= $filter->limit();
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalGeoZones() {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@geo_zone");
	}

	public function getZoneToGeoZones($geo_zone_id) {
		$query = $this->db->query("SELECT * FROM  @@zone_to_geo_zone WHERE geo_zone_id = " . (int)$geo_zone_id);

		return $query->rows;
	}

	public function getTotalZoneToGeoZoneByGeoZoneId($geo_zone_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@zone_to_geo_zone WHERE geo_zone_id = " . (int)$geo_zone_id);
	}

	public function getTotalZoneToGeoZoneByCountryId($country_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@zone_to_geo_zone WHERE country_id = " . (int)$country_id);
	}

	public function getTotalZoneToGeoZoneByZoneId($zone_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@zone_to_geo_zone WHERE zone_id = " . (int)$zone_id);
	}
}
?>